package org.noka.select;

import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.FilterConfig;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.nokatag.password.AESPlus;
import org.nokatag.system.Concest;
import org.nokatag.system.DBCon;
import org.nokatag.system.PreparedParemSet;
/**
 * 下拉框返回值
 * @author rebin
 *
 */
public class SelectData {
	public void getAjaxInputShows(HttpServletRequest request,HttpServletResponse response,FilterConfig config){
		StringBuffer body = new StringBuffer();
		Connection con = DBCon.getCon();
		try{
			response.setContentType("text/json");
			String sql = request.getParameter("sql");//加密数据
			String pare = request.getParameter("pare");//22,3,2
			if(sql==null || pare==null){
				body.append("[]");
			}else{
				sql=AESPlus.decrypt(sql,Concest.SQLKEY);//解密sql
				String[] pares = pare.split(",");
				String wsql = "?";
				for(int i=0;i<pares.length;i++){
					wsql+=",?";
				}
				con.setAutoCommit(true);
				sql = sql.replace("?",wsql);
				PreparedStatement msta = con.prepareStatement(sql);
				//----------------sql参数---------------------------
				msta.setObject(1,"0");
				for(int i=0;i<pares.length;i++){
					msta.setObject((i+2),pares[i]);
				}
				//--------------------------------------------------
				ResultSet min_rst =msta.executeQuery();
				ResultSetMetaData rsmd = min_rst.getMetaData();
				body.append("[");
				while(min_rst.next()){
					String value=min_rst.getString(1);
					String text="";
					if(rsmd.getColumnCount()>1)
						text=min_rst.getString(2);
					body.append("{");
					body.append("value:'"+jsonForm(value)+"',");
					body.append("vtype:'"+rsmd.getColumnType(1)+"',");
					//---------------组织附值属性-------------------------
					for(int cell= 1; cell<=rsmd.getColumnCount();cell++){
						String cellName = rsmd.getColumnName(cell);//列名
						String cellValue = min_rst.getString(cellName);//值
						body.append(cellName.toLowerCase()+":'"+jsonForm(cellValue)+"',");
					}
					//---------------------------------------------------
					body.append("text:'"+jsonForm(text)+"'");
					body.append("},");
				}
				if(body.length()>3)
					body.delete(body.length()-1, body.length());
				body.append("]");
				min_rst.close();
				msta.close();
			}
			//--------------输出----------------------------------
			OutputStream out = response.getOutputStream();
			Writer wr=new OutputStreamWriter(out,Concest.ENCODING);
			if(body!=null){
				wr.write(body.toString());
				wr.flush();
				out.flush();
				wr.close();
				out.close();
			}
		}catch(Exception se){
		}
	}
	/**
	 * <p>@Description ajax input获取选项 </p>
	 * <p>author rebin </p>
	 * <p>@Time 下午11:40:21</p>
	 * <p>xiefangjian@163.com</p>
	 * @param request
	 * @param response
	 * @param config
	 */
	public void getAjaxInputData(HttpServletRequest request,HttpServletResponse response,FilterConfig config){
		StringBuffer body = new StringBuffer();
		Connection con = DBCon.getCon();
		try{
			response.setContentType("text/json");
			String sql = request.getParameter("sql");//加密数据
			String pare = request.getParameter("pare");//查找时的SQL
			String[] v = request.getParameterValues("v");//当前控件值
			String   fsql = request.getParameter("fsql");//查找值
			//--------------------------------------------------
			String[] lower = request.getParameterValues("lower");//级联参数
			String[] lowev = request.getParameterValues("lowev");//级联参数类型
			if(sql==null){
				body.append("[]");
			}else{
				sql=AESPlus.decrypt(sql,Concest.SQLKEY);//解密sql
				con.setAutoCommit(true);
				PreparedStatement msta = con.prepareStatement(sql);
				//----------------sql参数---------------------------
				if(null!=lower && lower.length>0){
					setPare(msta,lower,lowev);//设置级联参数
					setPare(lower.length+1,msta,"%"+pare+"%",java.sql.Types.VARCHAR);
				}else{
					setPare(msta,"%"+pare+"%",java.sql.Types.VARCHAR);
				}
				//--------------------------------------------------
				SelectDataFindItem sfitem =addValues(fsql,v,lower,lowev,con);
				List<Map<String,String>> vlist = null;
				String findValue = null;
				if(null!=sfitem){
					vlist = sfitem.getVlist();
					findValue=sfitem.getJson();
				}
				//--------------------------------------------------
				ResultSet min_rst =msta.executeQuery();
				ResultSetMetaData rsmd = min_rst.getMetaData();
				body.append("[");
				body.append((null!=findValue && findValue.trim().length()>0)?findValue+",":"");
				while(min_rst.next()){
					String value=min_rst.getString(1);
					if(findValue(vlist,value)){
						continue;
					}
					String text="";
					if(rsmd.getColumnCount()>1)
						text=min_rst.getString(2);
					body.append("{");
					body.append("value:'"+jsonForm(value)+"',");
					body.append("vtype:'"+rsmd.getColumnType(1)+"',");
					//---------------组织附值属性-------------------------
					for(int cell= 1; cell<=rsmd.getColumnCount();cell++){
						String cellName = rsmd.getColumnName(cell);//列名
						String cellValue = min_rst.getString(cellName);//值
						body.append(cellName.toLowerCase()+":'"+jsonForm(cellValue)+"',");
					}
					//---------------------------------------------------
					body.append("text:'"+jsonForm(text)+"'");
					body.append("},");
				}
				if(body.length()>3)
					body.delete(body.length()-1, body.length());
				body.append("]");
				min_rst.close();
				msta.close();
			}
			//--------------输出----------------------------------
			OutputStream out = response.getOutputStream();
			Writer wr=new OutputStreamWriter(out,Concest.ENCODING);
			if(body!=null){
				wr.write(body.toString());
				wr.flush();
				out.flush();
				wr.close();
				out.close();
			}
		}catch(Exception se){
			se.printStackTrace();
		}
	}
	/**
	 * 获取select
	 * @param request
	 * @param response
	 * @param config
	 */
	public  void getData(HttpServletRequest request,HttpServletResponse response,FilterConfig config){
		StringBuffer body = new StringBuffer();
		Connection con = DBCon.getCon();
		try{
			response.setContentType("text/json");
			String sql = request.getParameter("sql");//加密数据
			String[] pare = request.getParameterValues("pare");//多级联动时的查询参数
			String[] v = request.getParameterValues("v");//当前控件值
			String   fsql = request.getParameter("fsql");//查找值
			String[] vtype = request.getParameterValues("vtype");//查询条件类型
			
			String[] lower = request.getParameterValues("lower");//级联参数
			String[] lowev = request.getParameterValues("lowev");//级联参数类型
			
			if(sql==null){
				body.append("[]");
			}else{
				sql=AESPlus.decrypt(sql,Concest.SQLKEY);//解密sql
				con.setAutoCommit(true);
				//--------------------------------------------------
				SelectDataFindItem sfitem =addValues(fsql,v,lower,lowev,con);
				List<Map<String,String>> vlist = null;
				String findValue = null;
				if(null!=sfitem){
					vlist = sfitem.getVlist();
					findValue=sfitem.getJson();
				}
				//-----------------------------------------------
				PreparedStatement msta = con.prepareStatement(sql);
				//----------------sql参数---------------------------
				if(sql.split("\\?").length<2 && null!=pare){//小于只有一个参数
					setPare(msta,new String[]{pare[pare.length-1]},vtype);
				}else{
					setPare(msta,pare,vtype);
				}
				//--------------------------------------------------
				ResultSet min_rst =msta.executeQuery();
				ResultSetMetaData rsmd = min_rst.getMetaData();
				body.append("[");
				body.append((null!=findValue && findValue.trim().length()>0)?findValue+",":"");
				while(min_rst.next()){
					String value=min_rst.getString(1);
					if(findValue(vlist,value)){
						continue;
					}
					String text="";
					if(rsmd.getColumnCount()>1)
						text=min_rst.getString(2);
					body.append("{");
					body.append("value:'"+jsonForm(value)+"',");
					body.append("vtype:'"+rsmd.getColumnType(1)+"',");
					//---------------组织附值属性-------------------------
					for(int cell= 1; cell<=rsmd.getColumnCount();cell++){
						String cellName = rsmd.getColumnName(cell);//列名
						String cellValue = min_rst.getString(cellName);//值
						body.append(cellName.toLowerCase()+":'"+jsonForm(cellValue)+"',");
					}
					//---------------------------------------------------
					body.append("text:'"+jsonForm(text)+"'");
					body.append("},");
				}
				if(body.length()>3)
					body.delete(body.length()-1, body.length());
				body.append("]");
				min_rst.close();
				msta.close();
			}
			//--------------输出----------------------------------
			OutputStream out = response.getOutputStream();
			Writer wr=new OutputStreamWriter(out,Concest.ENCODING);
			if(body!=null){
				wr.write(body.toString());
				wr.flush();
				out.flush();
				wr.close();
				out.close();
			}
		}catch(Exception se){
			//BugInfoWrint.Print(se);
		}
	}
	private void setPare(int Start,PreparedStatement msta,String pare,Integer vtype){
		if((null!=vtype)){
			setPare(Start,msta,new String[]{pare},new String[]{String.valueOf(vtype)});
		}
	}
	private void setPare(PreparedStatement msta,String pare,Integer vtype){
		if((null!=vtype)){
			setPare(1,msta,new String[]{pare},new String[]{String.valueOf(vtype)});
		}
	}
	private void setPare(PreparedStatement msta,String[] pare,String[] vtype){
		setPare(1, msta,pare,vtype);
	}
	//-------------------------------------------------------------------
	private boolean findValue(List<Map<String,String>>  vList,String value){
		
		if(null!=vList && !vList.isEmpty() && null!=value){
			for(Map<String,String> map:vList){
				if(jsonForm(value).equals(map.get("value"))){
					return true;
				}
			}
		}
		return false;
	}
	//-------------------------------------------------------------------
	private SelectDataFindItem addValues(String fsql,String[] v,String[] lower,String[] lowev, Connection con){
		List<Map<String,String>>  vList = new ArrayList<Map<String,String>>();
		StringBuffer body = new StringBuffer();
		try{
			if(null!=v && v.length>0 && null!=fsql){
				fsql=AESPlus.decrypt(fsql,Concest.SQLKEY);//解密sql select * from where text like ?
				fsql=" SELECT * FROM ("+fsql+") temptable_"+System.currentTimeMillis()+" where  VALUE IN (";
				for(int i=0;i<v.length;i++){
					fsql+="?,";
				}
				fsql = fsql.substring(0,fsql.length()-1);
				fsql+=")";
				PreparedStatement fmsta = con.prepareStatement(fsql);
				if(null!=lower && lower.length>0){
					setPare(fmsta,lower,lowev);//设置级联参数
					fmsta.setString(lower.length+1,"%");
					if(null!=v && v.length>0){
						setPare(lower.length+2,fmsta,v,null);
					}
				}else{
					fmsta.setString(1,"%");
					if(null!=v && v.length>0){
						setPare(2,fmsta,v,null);
					}
				}
				ResultSet fmin_rst =fmsta.executeQuery();
				ResultSetMetaData frsmd = fmin_rst.getMetaData();
				while(fmin_rst.next()){
					Map<String,String> map = new HashMap<String,String>();
					String value=fmin_rst.getString(1);
					String text="";
					if(frsmd.getColumnCount()>1)
						text=fmin_rst.getString(2);
					map.put("value", jsonForm(value));
					map.put("vtype", String.valueOf(frsmd.getColumnType(1)));
					//---------------组织附值属性-------------------------
					for(int cell= 1; cell<=frsmd.getColumnCount();cell++){
						String cellName = frsmd.getColumnName(cell);//列名
						String cellValue = fmin_rst.getString(cellName);//值
						map.put(cellName.toLowerCase(), jsonForm(cellValue));
					}
					//---------------------------------------------------
					map.put("text",jsonForm(text));
					vList.add(map);
				}
			}
		}catch(Exception se){
			se.printStackTrace();
		}
		try{
			if(!vList.isEmpty()){
				for(Map<String,String> map:vList){
					body.append("{");
					for (Map.Entry<String,String> entry : map.entrySet()) { 
					    body.append(entry.getKey()+":'"+entry.getValue()+"',");
					}
					if(body.toString().endsWith(","))
						body.delete(body.length()-1, body.length());
					body.append("},");
				}
				if(body.toString().endsWith(","))
					body.delete(body.length()-1, body.length());
			}
		}catch(Exception se){
		}
		return new SelectDataFindItem(body.toString(),vList);
	}
	//-------------------设置参数-----------------------------------------
	private void setPare(int start,PreparedStatement msta,String[] pare,String[] vtype){
		if(null!=pare && pare.length>0){
			for(int i=0;i<pare.length;i++){
				try{
					if(null!=pare[i]){
						try{
							if(null!=vtype && vtype.length>=pare.length){
								//msta.setObject((i+start),pare[i],Integer.parseInt(vtype[i]));
								PreparedParemSet preSet = new PreparedParemSet();
								preSet.setPre(msta,(i+start),pare[i],Integer.parseInt(vtype[i]));
							}else{
								msta.setObject((i+start),pare[i],java.sql.Types.BIGINT);
							}
						}catch(Exception se){
							msta.setObject((i+start),pare[i],java.sql.Types.VARCHAR);
						}
					}
				}catch(Exception se){
				}
			}
		}
	}
	public String jsonForm(String va){
		return va=(va==null?"":va.replace("\'","\\'").replace("\r","\\r").replace("\n","\\n").replace("\t","\\t"));
	}
}
